﻿// -----------------------------------------------------------------------
// <copyright file="User.cs" company="JSC Centido">
//  Copyright (c) JSC Centido. All rights reserved.
// </copyright>
// -----------------------------------------------------------------------

using System.Data.Odbc;
using System.Linq;

namespace Centido.Core
{
    using System;
    using System.Collections.Generic;


    /// <summary>
    ///   A registered user.
    /// </summary>
    public sealed class User : IRemovable, IUser, IRefreshable
    {
        /// <summary>
        ///   The current domain.
        /// </summary>
        private readonly Domain _domain;

        /// <summary>
        ///   The user name.
        /// </summary>
        private readonly string _userName;
        
        /// <summary>
        ///   The display name of the user.
        /// </summary>
        private string _displayName;

        /// <summary>
        ///   The user ability to logon.
        /// </summary>
        private bool? _canLogon;

        /// <summary>
        ///   The user ability to change the schema.
        /// </summary>
        private bool? _canChangeSchema;

        /// <summary>
        ///   The user ability to change security settings.
        /// </summary>
        private bool? _canChangeSecurity;

        /// <summary>
        ///   Initializes a new instance of the <see cref="Centido.Core.User"/> class
        /// for the specified user name and the specified domain.
        /// </summary>
        /// <param name = "userName">The user name.</param>
        /// <param name = "domain">The current domain.</param>
        /// <exception cref="ArgumentNullException">Thrown when the value of <paramref name="userName"/> is null.</exception>
        /// <exception cref="ArgumentException">Thrown when the value of <paramref name="userName"/> is an empty string.</exception>
        /// <exception cref="ArgumentNullException">Thrown when the value of <paramref name="domain"/> is null.</exception>
        internal User(string userName, Domain domain)
        {
            if (userName == null)
                throw new ArgumentNullException("userName");
            if (userName.Trim().Length == 0)
                throw new ArgumentException("User name cannot be empty.");
            if (domain == null)
                throw new ArgumentNullException("domain");

            _userName = userName.Trim().ToLower();
            _domain = domain;
        }

        /// <summary>
        ///   Gets the current domain.
        /// </summary>
        public Domain Domain
        {
            get { return _domain; }
        }

        /// <summary>
        ///   Gets or sets the user display name.
        /// </summary>
        /// <remarks>The current user has to have the permission to change security settings
        /// in order to set this property.</remarks>
        /// <exception cref="System.Data.Common.DbException">Thrown when the current user
        /// has no permission to set this property.</exception>
        /// <example>
        ///   The following example shows how to set the display name of a user.
        /// <code>
        ///     using System;
        ///     using Centido.Core;
        /// 
        ///     class Sample
        ///     {
        ///         static void Main()
        ///         {
        ///             var domainConfig = new DomainConfiguration()
        ///             {
        ///                 DbConnectionString = @"Driver={SQL Server};Server=.\SQLEXPRESS;Database=MyDb;Uid=admin;Pwd=P@ssw0rd;",
        ///                 DbProviderName = "MSSQLSERVER"
        ///             };
        ///         
        ///             var domain = Domain.Build(domainConfig);
        ///         
        ///             domain.DeleteAll();
        ///             
        ///             //Creating a user
        ///             var user = domain.AddUser("my_user", "P@ssw0rd");
        /// 
        ///             user.DisplayName = "The user display name";
        /// 
        ///             Console.WriteLine("User display name = {0}", user.DisplayName);
        ///         }
        ///     }
        /// </code>
        /// </example>
        public string DisplayName
        {
            get
            {
                // Reading from view!!!
                return _displayName ?? (_displayName = DbValue.GetString(
                    ReadProperty(Db.Views.UserNameList.Col.DisplayName, Db.Views.UserNameList.Name)));
            }
            set
            {
                if (value != null)
                    value = value.Trim();

                if (!string.IsNullOrEmpty(_displayName) && _displayName == value) return;

                // Writing to table (not the view)!!!
                WriteProperty(Db.Tables.UserList.Col.DisplayName, value, OdbcType.NVarChar);

                _displayName = value;
            }
        }

        /// <summary>
        ///   Gets or sets the user's ability to logon.
        /// </summary>
        /// <remarks>
        /// <para>The current user cannot change the logon ability of itself.</para>
        /// <para>The current user has to have the permission to change security
        /// settings in order to execute this function.</para>
        /// </remarks>
        /// <value>True if the user can logon; otherwise, false.</value>
        /// <exception cref="System.Data.Common.DbException">Thrown when the current user
        /// has no permission to set this property.</exception>
        /// <exception cref="System.Data.Common.DbException">Thrown when the current
        /// user is trying to change the logon ability of itself.</exception>
        /// <example>
        ///   The following example shows how to change the user logon ability.
        /// <code>
        ///     using System;
        ///     using System.Data.Common;
        ///     using Centido.Core;
        /// 
        ///     class Sample
        ///     {
        ///         static void Main()
        ///         {
        ///             var domainConfig = new DomainConfiguration()
        ///             {
        ///                 DbConnectionString = @"Driver={SQL Server};Server=.\SQLEXPRESS;Database=MyDb;Uid=admin;Pwd=P@ssw0rd;",
        ///                 DbProviderName = "MSSQLSERVER"
        ///             };
        ///         
        ///             var domain = Domain.Build(domainConfig);
        ///         
        ///             domain.DeleteAll();
        ///             
        ///             //Creating a user
        ///             var user = domain.AddUser("my_user", "P@ssw0rd");
        /// 
        ///             //Connecting as a new user
        ///             domain.Logon("my_user", "P@ssw0rd");
        /// 
        ///             //Forbidding the user to logon
        ///             user.CanLogon = false;
        /// 
        ///             try
        ///             {
        ///                 domain.Logon("my_user", "P@ssw0rd");
        ///             }
        ///             catch(DbException)
        ///             {
        ///                 Console.WriteLine("The user is not allowed to logon");
        ///             }
        ///         }
        ///     }
        /// </code>
        /// </example>
        public bool CanLogon
        {
            get { return _canLogon ?? (_canLogon = DbValue.GetInteger(
                ReadProperty(Db.Tables.UserList.Col.CanLogon)) == 1).Value; }
            set
            {
                if (_canLogon == value) return;

                Domain.DbAccess.ExecuteNonQuery(
                    cmd =>
                    {
                        cmd.CommandText = string.Format("{{call {0}(?,?)}}",
                            Domain.GetObjectFullName(Db.Procedures.SetUserCanLogon));

                        cmd.Parameters.Add(Domain.DbAccess.CreateParameter("a_user_name", OdbcType.NVarChar, Name));
                        cmd.Parameters.Add(Domain.DbAccess.CreateParameter("a_can_login", OdbcType.Int, value ? 1 : 0));
                    });

                _canLogon = value;
            }
        }

        /// <summary>
        ///   Gets or sets the user's ability to change the schema.
        /// </summary>
        /// <remarks>
        /// <para>Changing the schema means defining the table structure,
        /// adding table indexes etc.</para>
        /// <para>The current user has to have the permission to change security
        /// settings in order to set this property.</para></remarks>
        /// <exception cref="System.Data.Common.DbException">Thrown when the current user has
        /// no permission to set this property.</exception>
        /// <value>True if the user can change the schema; otherwise, false.</value>
        /// <example>
        ///   The following example shows how to enable the user ability to change the schema.
        /// <code>
        ///     using System;
        ///     using System.Data.Common;
        ///     using Centido.Core;
        /// 
        ///     class Sample
        ///     {
        ///         static void Main()
        ///         {
        ///             var domainConfig = new DomainConfiguration()
        ///             {
        ///                 DbConnectionString = @"Driver={SQL Server};Server=.\SQLEXPRESS;Database=MyDb;Uid=admin;Pwd=P@ssw0rd;",
        ///                 DbProviderName = "MSSQLSERVER"
        ///             };
        ///         
        ///             var domain = Domain.Build(domainConfig);
        ///         
        ///             domain.DeleteAll();
        ///             
        ///             //Creating a user
        ///             var user = domain.AddUser("my_user", "P@ssw0rd");
        /// 
        ///             //Connecting as a new user
        ///             var userDomain = domain.Logon("my_user", "P@ssw0rd");
        /// 
        ///             //Trying to add a new table
        ///             try
        ///             {
        ///                 userDomain.AddTable("my_table");
        ///             }
        ///             catch(DbException)
        ///             {
        ///                 Console.WriteLine("The user has no permission to add a table");
        ///             }
        /// 
        ///             Console.WriteLine("Granting the user the permission to change the schema");
        /// 
        ///             user.CanChangeSchema = true;
        /// 
        ///             //Creating a new table
        ///             userDomain.AddTable("my_table");
        ///         }
        ///     }
        /// </code>
        /// </example>
        public bool CanChangeSchema
        {
            get { return _canChangeSchema ?? (_canChangeSchema = DbValue.GetInteger(
                ReadProperty(Db.Tables.UserList.Col.CanChangeSchema)) == 1).Value; }
            set
            {
                if (_canChangeSchema == value) return;

                Domain.DbAccess.ExecuteNonQuery(
                    cmd =>
                    {
                        cmd.CommandText = string.Format("{{call {0}(?,?)}}",
                            Domain.GetObjectFullName(Db.Procedures.SetUserCanChangeSchema));

                        cmd.Parameters.Add(Domain.DbAccess.CreateParameter("a_user_name",
                            OdbcType.NVarChar, Name));
                        cmd.Parameters.Add(Domain.DbAccess.CreateParameter("a_can_change_schema",
                            OdbcType.Int, value ? 1 : 0));
                    });

                _canChangeSchema = value;
            }
        }

        /// <summary>
        ///   Gets or sets the user's ability to change security settings.
        /// </summary>
        /// <remarks>
        /// <para>The current user is not allowed to change this property of itself.</para>
        /// <para>The current user has to have the permission to change security settings
        /// in order to set this property.</para>
        /// </remarks>
        /// <value>True if the user can change security settings; otherwise, false.</value>
        /// <exception cref="System.Data.Common.DbException">Thrown when the current user
        /// has no permission to set this property.</exception>
        /// <exception cref="System.Data.Common.DbException">Thrown when the current user
        /// is trying to change this property of itself.</exception>
        /// <example>
        ///   The following example shows how to enable the user ability to change security settings.
        /// <code>
        ///     using System;
        ///     using System.Data.Common;
        ///     using Centido.Core;
        /// 
        ///     class Sample
        ///     {
        ///         static void Main()
        ///         {
        ///             var domainConfig = new DomainConfiguration()
        ///             {
        ///                 DbConnectionString = @"Driver={SQL Server};Server=.\SQLEXPRESS;Database=MyDb;Uid=admin;Pwd=P@ssw0rd;",
        ///                 DbProviderName = "MSSQLSERVER"
        ///             };
        ///         
        ///             var domain = Domain.Build(domainConfig);
        ///         
        ///             domain.DeleteAll();
        ///             
        ///             //Creating a user
        ///             var user = domain.AddUser("my_user", "P@ssw0rd");
        /// 
        ///             //Connecting as a new user
        ///             var userDomain = domain.Logon("my_user", "P@ssw0rd");
        /// 
        ///             //Trying to add a new user
        ///             try
        ///             {
        ///                 userDomain.AddUser("my_new_user", "P@ssw0rd");
        ///             }
        ///             catch(DbException)
        ///             {
        ///                 Console.WriteLine("The user has no permission to add a new user");
        ///             }
        /// 
        ///             Console.WriteLine("Granting the user the permission to change security settings");
        /// 
        ///             user.CanChangeSecurity = true;
        /// 
        ///             //Creating a new user
        ///             userDomain.AddUser("my_new_user", "P@ssw0rd");
        ///         }
        ///     }
        /// </code>
        /// </example>
        public bool CanChangeSecurity
        {
            get { return _canChangeSecurity ?? (_canChangeSecurity = DbValue.GetInteger(
                ReadProperty(Db.Tables.UserList.Col.CanChangeSecurity)) == 1).Value; }
            set
            {
                if (_canChangeSecurity == value) return;

                Domain.DbAccess.ExecuteNonQuery(
                    cmd =>
                    {
                        cmd.CommandText = string.Format("{{call {0}(?,?)}}", 
                            Domain.GetObjectFullName(Db.Procedures.SetUserCanChangeSecurity));

                        cmd.Parameters.Add(Domain.DbAccess.CreateParameter("a_user_name",
                            OdbcType.NVarChar, Name));
                        cmd.Parameters.Add(Domain.DbAccess.CreateParameter("a_can_change_security",
                            OdbcType.Int, value ? 1 : 0));
                    });

                _canChangeSecurity = value;
            }
        }

        /// <summary>
        ///   Gets the user business units.
        /// </summary>
        /// <example>
        ///   The following example shows how to get the user busines units.
        /// <code>
        ///     using System;
        ///     using Centido.Core;
        /// 
        ///     class Sample
        ///     {
        ///         static void Main()
        ///         {
        ///             var domainConfig = new DomainConfiguration()
        ///             {
        ///                 DbConnectionString = @"Driver={SQL Server};Server=.\SQLEXPRESS;Database=MyDb;Uid=admin;Pwd=P@ssw0rd;",
        ///                 DbProviderName = "MSSQLSERVER"
        ///             };
        ///         
        ///             var domain = Domain.Build(domainConfig);
        ///         
        ///             domain.DeleteAll();
        ///             
        ///             //Creating a user
        ///             var user = domain.AddUser("my_user", "P@ssw0rd");
        /// 
        ///             //Adding the user to different units
        ///             var firstUnit = domain.AddUnit("first_unit");
        ///             firstUnit.DisplayName = "The first unit";
        /// 
        ///             var secondUnit = domain.AddUnit("second_unit");
        ///             secondUnit.DisplayName = "The second unit";
        /// 
        ///             var thirdUnit = domain.AddUnit("third_unit");
        ///             thirdUnit.DisplayName = "The third unit";
        /// 
        ///             firstUnit.AddUser(user);
        ///             secondUnit.AddUser(user);
        ///             
        ///             Console.WriteLine("User units:");
        /// 
        ///             foreach(var u in user.Units)
        ///                 Console.WriteLine("\t{0}", u.DisplayName);
        ///         }
        ///     }
        /// </code>
        /// </example>
        public ICollection<Unit> Units
        {
            get
            {
                var resultList = new List<Unit>();

                Domain.DbAccess.ExecuteReader(
                    cmd =>
                    {
                        cmd.CommandText = string.Format(
                            "select {0},{1} from {2} where {3} in (select {4} from {5} where {6}=?)",
                            Db.Tables.UnitList.Col.UnitId,
                            Db.Tables.UnitList.Col.UnitName,
                            Domain.GetObjectFullName(Db.Tables.UnitList.Name),
                            Db.Tables.UnitList.Col.UnitId,
                            Db.Tables.UserUnitList.Col.UnitId,
                            Domain.GetObjectFullName(Db.Tables.UserUnitList.Name),
                            Db.Tables.UserUnitList.Col.UserName);

                        cmd.Parameters.Add(Domain.DbAccess.CreateParameter("user_name", OdbcType.NVarChar, Name));
                    },
                    reader =>
                    {
                        // The unit id
                        var unitId = reader.GetGuid(Db.Tables.UnitList.Col.UnitId).GetValueOrDefault();

                        // The unit name
                        var unitName = reader.GetString(Db.Tables.UnitList.Col.UnitName);

                        resultList.Add(new Unit(unitId, unitName, Domain));
                    });

                return resultList;
            }
        }

        /// <summary>
        ///   Gets the user roles.
        /// </summary>
        /// <remarks>The current user has to have the permission to change
        /// security settings in order to read this property.</remarks>
        /// <exception cref="System.Data.Common.DbException">Thrown when the current user has no permission
        /// to read this property.</exception>
        /// <example>
        ///   The following example shows how to read the user roles,
        /// <code>
        ///     using System;
        ///     using Centido.Core;
        /// 
        ///     class Sample
        ///     {
        ///         static void Main()
        ///         {
        ///             var domainConfig = new DomainConfiguration()
        ///             {
        ///                 DbConnectionString = @"Driver={SQL Server};Server=.\SQLEXPRESS;Database=MyDb;Uid=admin;Pwd=P@ssw0rd;",
        ///                 DbProviderName = "MSSQLSERVER"
        ///             };
        ///         
        ///             var domain = Domain.Build(domainConfig);
        ///         
        ///             domain.DeleteAll();
        ///             
        ///             //Creating a user
        ///             var user = domain.AddUser("my_user", "P@ssw0rd");
        /// 
        ///             //Adding the user to different roles
        ///             var firstRole = domain.AddRole("first_role");
        ///             var secondRole = domain.AddRole("second_role");
        ///             var thirdRole = domain.AddRole("third_role");
        /// 
        ///             firstRole.AddUser(user);
        ///             secondRole.AddUser(user);
        ///             
        ///             Console.WriteLine("User roles:");
        /// 
        ///             foreach(var r in user.Roles)
        ///                 Console.WriteLine("\t{0}", r.Name);
        ///         }
        ///     }
        /// </code>
        /// </example>
        public ICollection<Role> Roles
        {
            get
            {
                var resultList = new List<Role>();

                Domain.DbAccess.ExecuteReader(
                    cmd =>
                    {
                        cmd.CommandText = string.Format(
                            "select {0} from {1} where {2}=?",
                            Db.Tables.UserRolesList.Col.ContainerRoleName,
                            Domain.GetObjectFullName(Db.Tables.UserRolesList.Name),
                            Db.Tables.UserRolesList.Col.UserName);

                        cmd.Parameters.Add(Domain.DbAccess.CreateParameter("user_name", OdbcType.NVarChar, Name));
                    },
                    reader => resultList.Add(new Role(reader.GetString(Db.Tables.UserRolesList.Col.ContainerRoleName), Domain)));

                return resultList;
            }
        }

        /// <summary>
        ///   Gets the user table permissions.
        /// </summary>
        /// <remarks>The current user has to have the permission to change
        /// security settings in order to execute this function.</remarks>
        /// <exception cref="System.Data.Common.DbException">Thrown when the current user
        /// has no permission to read this property.</exception>
        /// <example>
        ///   The following example shows how to get the user table permissions.
        /// <code>
        ///     using System;
        ///     using Centido.Core;
        /// 
        ///     class Sample
        ///     {
        ///         static void Main()
        ///         {
        ///             var domainConfig = new DomainConfiguration()
        ///             {
        ///                 DbConnectionString = @"Driver={SQL Server};Server=.\SQLEXPRESS;Database=MyDb;Uid=admin;Pwd=P@ssw0rd;",
        ///                 DbProviderName = "MSSQLSERVER"
        ///             };
        ///         
        ///             var domain = Domain.Build(domainConfig);
        ///         
        ///             domain.DeleteAll();
        /// 
        ///             //Creating two tables
        ///             var firstTable = domain.AddTable("first_table");
        ///             var secondTable = domain.AddTable("second_table");
        ///             
        ///             //Creating a user
        ///             var user = domain.AddUser("my_user", "P@ssw0rd");
        /// 
        ///             //Granting table permission to the user
        ///             firstTable.GrantAll(user);
        ///             secondTable.Grant(user, DataAction.Select, AccessLevel.Unit);
        /// 
        ///             Console.WriteLine("User table permissions:");
        /// 
        ///             foreach(var perm in user.TablePermissions)
        ///                 Console.WriteLine("\tTable = {0}, Principal = {1}, action = {2}, level = {3}",
        ///                     perm.Table.Name, perm.Principal.Name, perm.DataAction, perm.AccessLevel);
        ///         }
        ///     }
        /// </code>
        /// </example>
        public ICollection<TablePermission> TablePermissions
        {
            get
            {
                var resultList = new List<TablePermission>();

                Domain.DbAccess.ExecuteReader(
                    cmd =>
                    {
                        cmd.CommandText = string.Format(
                            "select {0}, {1} from {2} where {3}=?",
                            Db.Tables.TableAcl.Col.TableId,
                            Db.Tables.TableAcl.Col.DataAction,
                            Domain.GetObjectFullName(Db.Tables.TableAcl.Name),
                            Db.Tables.TableAcl.Col.PrincipalName);

                        cmd.Parameters.Add(Domain.DbAccess.CreateParameter("principal_name", OdbcType.NVarChar, Name));
                    },
                    reader =>
                    {
                        var tableId = reader.GetGuid(Db.Tables.TableAcl.Col.TableId)
                            .GetValueOrDefault();
                        var dataActionValue = reader.GetInteger(Db.Tables.TableAcl.Col.DataAction)
                            .GetValueOrDefault();

                        var dataAction = (DataAction)dataActionValue;

                        resultList.Add(new TablePermission(new Table(tableId, Domain), Name, dataAction));
                    });

                return resultList;
            }
        }

        /// <summary>
        ///   Gets the user table access rights.
        /// </summary>
        /// <remarks>The current user has to have the permission to change security settings in
        /// order to read this property.</remarks>
        /// <exception cref="System.Data.Common.DbException">Thrown when the current user has no permission
        /// to read this property.</exception>
        /// <example>
        ///   The following example shows how to read the user table access rights.
        /// <code>
        ///     using System;
        ///     using Centido.Core;
        /// 
        ///     class Sample
        ///     {
        ///         static void Main()
        ///         {
        ///             var domainConfig = new DomainConfiguration()
        ///             {
        ///                 DbConnectionString = @"Driver={SQL Server};Server=.\SQLEXPRESS;Database=MyDb;Uid=admin;Pwd=P@ssw0rd;",
        ///                 DbProviderName = "MSSQLSERVER"
        ///             };
        ///         
        ///             var domain = Domain.Build(domainConfig);
        ///         
        ///             domain.DeleteAll();
        /// 
        ///             //Creating three users
        ///             var firstUser = domain.AddUser("first_user", "P@ssw0rd");
        ///             var secondUser = domain.AddUser("second_user", "P@ssw0rd");
        ///             var thirdUser = domain.AddUser("third_user", "P@ssw0rd");
        /// 
        ///             //Creating a table
        ///             var table = domain.AddTable("my_table");
        /// 
        ///             //Adding the first two users to the same unit
        ///             var unit = domain.AddUnit("my_unit");
        /// 
        ///             unit.AddUser(firstUser);
        ///             unit.AddUser(secondUser);
        /// 
        ///             //Granting the first user the permission to select from the table
        ///             table.Grant(firstUser, DataAction.Select);
        /// 
        ///             //Granting the first user the permission to update rows of the users from the same unit
        ///             table.Grant(firstUser, DataAction.Update, AccessLevel.Unit);
        /// 
        ///             Console.WriteLine("User table access rights:");
        /// 
        ///             foreach(var r in firstUser.TableAccessRights)
        ///                 if (r is TableFullAccessRight)
        ///                     Console.WriteLine("Full access: Table = {0}, User = {1}, Action = {2}",
        ///                         r.Table.Name, r.User.Name, r.DataAction);
        ///                 else if (r is TableAccessRight)
        ///                     Console.WriteLine("Partial access: Table = {0}, User = {1}, Owner = {2}, Action = {3}",
        ///                         r.Table.Name, r.User.Name, (r as TableAccessRight).Owner.Name , r.DataAction);
        ///         }
        ///     }
        /// </code>
        /// </example>
        public ICollection<ITableAccessRight> TableAccessRights
        {
            get
            {
                var resultList = new List<ITableAccessRight>();

                // Reading full access rights
                resultList.AddRange(TableFullAccessRights.OfType<ITableAccessRight>());

                // Reading own rows access rights
                resultList.AddRange(TableOwnRowsAccessRights.OfType<ITableAccessRight>());

                // Reading unit-level access rights
                resultList.AddRange(TableUnitAccessRights.OfType<ITableAccessRight>());

                return resultList;
            }
        }


        /// <summary>
        ///   Gets a list of the user own rows table access rights.
        /// </summary>
        /// <remarks>The current user has to have the permission to change security settings in
        /// order to read this property.</remarks>
        /// <exception cref="System.Data.Common.DbException">Thrown when the current user has no permission
        /// to read this property.</exception>
        public ICollection<TableOwnRowsAccessRight> TableOwnRowsAccessRights
        {
            get 
            {
                var resultList = new List<TableOwnRowsAccessRight>();

                Domain.DbAccess.ExecuteReader(
                    cmd =>
                    {
                        cmd.CommandText = string.Format(
                            "select {0}, {1} from {2} where {3}=?",
                            Db.Tables.TableAclOwnRows.Col.TableId,
                            Db.Tables.TableAclOwnRows.Col.DataAction,
                            Domain.GetObjectFullName(Db.Tables.TableAclOwnRows.Name),
                            Db.Tables.TableAclOwnRows.Col.UserName);

                        cmd.Parameters.Add(Domain.DbAccess.CreateParameter("user_name", OdbcType.NVarChar, Name));
                    },
                    reader =>
                    {
                        var tableId = reader.GetGuid(Db.Tables.TableAclOwnRows.Col.TableId)
                            .GetValueOrDefault();
                        var dataActionValue = reader.GetInteger(Db.Tables.TableAclOwnRows.Col.DataAction)
                            .GetValueOrDefault();

                        var dataAction = (DataAction)dataActionValue;

                        resultList.Add(new TableOwnRowsAccessRight(new Table(tableId, Domain), this, dataAction));
                    });

                return resultList;
            }
        }


        /// <summary>
        ///   Gets a list of the user full table access rights.
        /// </summary>
        /// <remarks>The current user has to have the permission to change security settings in
        /// order to read this property.</remarks>
        /// <exception cref="System.Data.Common.DbException">Thrown when the current user has no permission
        /// to read this property.</exception>
        public ICollection<TableFullAccessRight> TableFullAccessRights
        {
            get
            {
                var resultList = new List<TableFullAccessRight>();

                // Reading full access rights
                Domain.DbAccess.ExecuteReader(
                    cmd =>
                    {
                        cmd.CommandText = string.Format(
                            "select {0}, {1} from {2} where {3}=?",
                            Db.Tables.TableAclFullAccess.Col.TableId,
                            Db.Tables.TableAclFullAccess.Col.DataAction,
                            Domain.GetObjectFullName(Db.Tables.TableAclFullAccess.Name),
                            Db.Tables.TableAclFullAccess.Col.UserName);

                        cmd.Parameters.Add(Domain.DbAccess.CreateParameter("user_name", OdbcType.NVarChar, Name));
                    },
                    reader =>
                    {
                        var tableId = reader.GetGuid(Db.Tables.TableAclFullAccess.Col.TableId)
                            .GetValueOrDefault();
                        var dataActionValue = reader.GetInteger(Db.Tables.TableAclFullAccess.Col.DataAction)
                            .GetValueOrDefault();

                        var dataAction = (DataAction)dataActionValue;

                        resultList.Add(new TableFullAccessRight(new Table(tableId, Domain), this, dataAction));
                    });

                return resultList;
            }
        }


        /// <summary>
        ///   Gets a list of the user unit-level access rights.
        /// </summary>
        /// <remarks>The current user has to have the permission to change security settings in
        /// order to read this property.</remarks>
        /// <exception cref="System.Data.Common.DbException">Thrown when the current user has no permission
        /// to read this property.</exception>
        public ICollection<TableUnitAccessRight> TableUnitAccessRights
        {
            get 
            {
                var resultList = new List<TableUnitAccessRight>();

                Domain.DbAccess.ExecuteReader(
                    cmd =>
                    {
                        cmd.CommandText = string.Format(
                            "select {0}, {1}, {2} from {3} where {4}=?",
                            Db.Tables.TableAclUnitLevelAccess.Col.TableId,
                            Db.Tables.TableAclUnitLevelAccess.Col.UnitId,
                            Db.Tables.TableAclUnitLevelAccess.Col.DataAction,
                            Domain.GetObjectFullName(Db.Tables.TableAclUnitLevelAccess.Name),
                            Db.Tables.TableAclUnitLevelAccess.Col.UserName);

                        cmd.Parameters.Add(Domain.DbAccess.CreateParameter("user_name", OdbcType.NVarChar, Name));
                    },
                    reader =>
                    {
                        // The table id
                        var tableId = reader.GetGuid(Db.Tables.TableAclFullAccess.Col.TableId)
                            .GetValueOrDefault();
                        // The unit id
                        var unitId = reader.GetGuid(Db.Tables.TableAclUnitLevelAccess.Col.UnitId)
                            .GetValueOrDefault();
                        // The data action
                        var dataAction = (DataAction)reader.GetInteger(Db.Tables.TableAclFullAccess.Col.DataAction)
                            .GetValueOrDefault();

                        resultList.Add(new TableUnitAccessRight(
                                new Table(tableId, Domain), this, unitId, dataAction));
                    });

                return resultList;
            }
        }

        /// <summary>
        ///   Gets the user name.
        /// </summary>
        public string Name
        {
            get { return _userName; }
        }

        /// <summary>
        ///   Deletes the user.
        /// </summary>
        /// <remarks>
        /// <para>The current user is not allowed to delete itself.</para>
        /// <para>The current user has to have the permission to change security
        /// settings in order to execute this function.</para>
        /// </remarks>
        /// <exception cref="System.Data.Common.DbException">Thrown when the current user
        /// has no permission to execute this function.</exception>
        /// <exception cref="System.Data.Common.DbException">Thrown when the current
        /// user is trying to delete itself.</exception>
        /// <example>
        ///   The following example shows how to delete a user.
        /// <code>
        ///     using System;
        ///     using Centido.Core;
        /// 
        ///     class Sample
        ///     {
        ///         static void Main()
        ///         {
        ///             var domainConfig = new DomainConfiguration()
        ///             {
        ///                 DbConnectionString = @"Driver={SQL Server};Server=.\SQLEXPRESS;Database=MyDb;Uid=admin;Pwd=P@ssw0rd;",
        ///                 DbProviderName = "MSSQLSERVER"
        ///             };
        ///         
        ///             var domain = Domain.Build(domainConfig);
        ///         
        ///             domain.DeleteAll();
        ///             
        ///             //Creating a user
        ///             var user = domain.AddUser("my_user", "P@ssw0rd");
        /// 
        ///             //Adding the user to a security role
        ///             var role = domain.AddRole("my_role");
        ///             role.AddUser(user);
        /// 
        ///             //Adding the user to a unit
        ///             var unit = domain.AddUnit("my_unit");
        ///             unit.AddUser(user);
        /// 
        ///             Console.WriteLine("Role: user count = {0}", role.Users.Count());
        ///             Console.WriteLine("Unit: user count = {0}", unit.Users.Count());
        /// 
        ///             Console.WriteLine("Deleting the user");
        /// 
        ///             user.Delete();
        /// 
        ///             Console.WriteLine("Role: user count = {0}", role.Users.Count());
        ///             Console.WriteLine("Unit: user count = {0}", unit.Users.Count());
        ///         }
        ///     }
        /// </code>
        /// </example>
        public void Delete()
        {
            Domain.DbAccess.ExecuteNonQuery(
                cmd =>
                {
                    cmd.CommandText = string.Format("{{call {0}(?)}}", 
                        Domain.GetObjectFullName(Db.Procedures.DeleteUser));

                    cmd.Parameters.Add(Domain.DbAccess.CreateParameter("a_user_name", OdbcType.NVarChar, Name));
                });

            // Deleting the user from the cache
            Domain.DeleteUserFromCache(this);
        }

        /// <summary>
        ///   Checks the existence of the user.
        /// </summary>
        /// <returns>Returns true if the user still exists; otherwise, returns false.</returns>
        public bool Exists()
        {
            var wasFound = false;

            Domain.DbAccess.ExecuteScalar(
                cmd =>
                {
                    cmd.CommandText = string.Format("select count(*) from {0} where {1}=?",
                        Domain.GetObjectFullName(Db.Tables.UserList.Name),
                        Db.Tables.UserList.Col.UserName);

                    cmd.Parameters.Add(Domain.DbAccess.CreateParameter("name", OdbcType.NVarChar, Name));
                },
                result => wasFound = DbValue.GetInteger(result) != 0);

            return wasFound;
        }

        /// <summary>
        ///   Resets the user password.
        /// </summary>
        /// <remarks>The current user has to have the permission to change
        /// security settings in order to execute this function.</remarks>
        /// <param name = "newPassword">The user new password.</param>
        /// <exception cref="ArgumentNullException">Thrown when the value of <paramref name="newPassword"/> is null.</exception>
        /// <exception cref="ArgumentException">Thrown when the value of <paramref name="newPassword"/> is an empty string.</exception>
        /// <exception cref="System.Data.Common.DbException">Thrown when the current user has no permission to execute this function.</exception>
        /// <example>
        ///   The following example shows how to reset a password of a user.
        /// <code>
        ///     using System;
        ///     using System.Data.Common;
        ///     using Centido.Core;
        /// 
        ///     class Sample
        ///     {
        ///         static void Main()
        ///         {
        ///             var domainConfig = new DomainConfiguration()
        ///             {
        ///                 DbConnectionString = @"Driver={SQL Server};Server=.\SQLEXPRESS;Database=MyDb;Uid=admin;Pwd=P@ssw0rd;",
        ///                 DbProviderName = "MSSQLSERVER"
        ///             };
        ///         
        ///             var domain = Domain.Build(domainConfig);
        ///         
        ///             domain.DeleteAll();
        ///             
        ///             //Creating a user
        ///             var user = domain.AddUser("my_user", "P@ssw0rd");
        /// 
        ///             //Connecting as a new user
        ///             domain.Logon("my_user", "P@ssw0rd");
        /// 
        ///             Console.WriteLine("Resetting the user password");
        /// 
        ///             user.ChangePassword("NewP@ssw0rd");
        /// 
        ///             try
        ///             {
        ///                 domain.Logon("my_user", "P@ssw0rd");
        ///             }
        ///             catch(DbException)
        ///             {
        ///                 Console.WriteLine("Connecting with the old password is forbidden");
        ///             }
        /// 
        ///             domain.Logon("my_user", "NewP@ssw0rd");
        ///         }
        ///     }
        /// </code>
        /// </example>
        public void ChangePassword(string newPassword)
        {
            if (newPassword == null)
                throw new ArgumentNullException("newPassword");
            if (newPassword.Trim().Length == 0)
                throw new ArgumentException("New password cannot be empty.");

            Domain.DbAccess.ExecuteNonQuery(
                cmd =>
                {
                    cmd.CommandText = string.Format("{{call {0}(?,?)}}",
                        Domain.GetObjectFullName(Db.Procedures.ChangeUserPassword));

                    cmd.Parameters.Add(Domain.DbAccess.CreateParameter("a_user_name", OdbcType.NVarChar, Name));
                    cmd.Parameters.Add(Domain.DbAccess.CreateParameter("a_new_password", OdbcType.NVarChar, newPassword));
                });
        }


        /// <summary>
        ///   True if this user is currently logged in.
        /// </summary>
        public bool IsCurrentUser
        {
            get { return Name == Domain.CurrentUser.Name; }
        }


        /// <summary>
        ///   Reads the user property.
        /// </summary>
        /// <param name = "propertyName">The property name.</param>
        /// <param name = "tableName">The target table name.</param>
        /// <returns>The property value.</returns>
        private object ReadProperty(string propertyName, string tableName = Db.Tables.UserList.Name)
        {
            object propertyValue = null;

            _domain.DbAccess.ExecuteScalar(
                cmd =>
                {
                    cmd.CommandText = string.Format(
                        "select {0} from {1} where {2}=?",
                        propertyName,
                        Domain.GetObjectFullName(tableName),
                        Db.Tables.UserList.Col.UserName);

                    cmd.Parameters.Add(Domain.DbAccess.CreateParameter("user_name", OdbcType.NVarChar, Name));
                },
                result => propertyValue = result);

            return propertyValue;
        }

        /// <summary>
        ///   Update the user property.
        /// </summary>
        /// <param name = "propertyName">The property name.</param>
        /// <param name = "propertyValue">The property value.</param>
        /// <param name = "propertyType">The property database type.</param>
        private void WriteProperty(string propertyName, object propertyValue, OdbcType propertyType)
        {
            var rowCount = _domain.DbAccess.ExecuteNonQuery(
                cmd =>
                {
                    cmd.CommandText = string.Format(
                        "update {0} set {1}=? where {2}=?",
                        Domain.GetObjectFullName(Db.Tables.UserList.Name),
                        propertyName,
                        Db.Tables.UserList.Col.UserName);

                    cmd.Parameters.Add(
                        Domain.DbAccess.CreateParameter("value", propertyType, DbValue.GetDbValue(propertyValue)));
                    cmd.Parameters.Add(Domain.DbAccess.CreateParameter("user_name", OdbcType.NVarChar, Name));
                });

            if (rowCount < 1)
                throw new DbOperationException();
        }

        /// <summary>
        ///   Refreshes the properties of the user.
        /// </summary>
        public void Refresh()
        {
            _displayName = null;
            _canLogon = null;
            _canChangeSchema = null;
            _canChangeSecurity = null;
        }
    }
}